package bainuo

import groovy.sql.Sql

def execute() {
    def conn = null
    try {
        // 创建数据库连接
        conn = groovy.sql.Sql.newInstance(mypara.hqcon(2))
        List<Map<String, String>> results = new ArrayList<>()

        // 查询语句
        def sql = """
            select * from 
    (select cpraybillbid 订单请购单表体id, pb.pk_order_b 订单明细id, p.dbilldate 订单日期, p.vbillcode 订单号, p.taudittime 订单审核日期, 
            a1.code 物料编码, a1.name 物料名称, a2.name 计量单位, pb.nnum 订单数量, pb.nqtorigprice 订单无税单价, pb.nqtorigtaxprice 订单含税单价, 
            pb.norigmny 订单无税金额, pb.norigtaxmny 订单含税金额, pb.ntax 订单税额, pb.ntaxrate 订单税率, pb.dplanarrvdate 计划到货日期 
     from po_order p  --采购订单主表
     inner join po_order_b pb on p.pk_order = pb.pk_order  --采购订单子表
     inner join bd_material a1 on pb.pk_material = a1.pk_material 
     inner join bd_measdoc a2 on pb.cunitid = a2.pk_measdoc --物料
     where p.dr = 0 and pb.dr = 0 and a1.dr = 0 and a2.dr = 0
    ) z2  --采购订单信息
    left join 
    (select pob.pk_order_b 到货单订单明细id, pob.pk_arriveorder_b 到货单明细id, po.vbillcode 到货单号, po.dbilldate 到货日期, pob.nnum 到货数量, pob.ntaxmny 到货金额 
     from po_arriveorder po  --到货单主表
     inner join po_arriveorder_b pob on po.pk_arriveorder = pob.pk_arriveorder  --到货单子表
     where po.dr = 0 and pob.dr = 0
    ) z3 --到货单信息
    on z2.订单明细id = z3.到货单订单明细id  --到货单关联订单
    left join
    (select icb.csourcetype 入库单来源单据类型id, icb.csourcebillbid 入库单来源单据id, icb.cgeneralbid 入库单明细id, ich.vbillcode 入库单号, ich.dbilldate 入库单日期, icb.nnum 入库数量, icb.ntaxmny 入库金额 
     from ic_purchasein_h ich --采购入库单主表
     inner join ic_purchasein_b icb on ich.cgeneralhid = icb.cgeneralhid  --采购入库单子表
     where ich.dr = 0 and icb.dr = 0 and icb.csourcetype = '23'
    ) z4 on z3.到货单明细id = z4.入库单来源单据id  --采购入库单信息
    left join 
    (select pob2.pk_order_b 发票订单id, pob2.csourcebid 发票入库单明细id, po2.vbillcode 发票号, vordercode, po2.dbilldate 发票日期, pob2.nnum 发票数量, pob2.ntaxmny 发票金额 
     from po_invoice po2 --采购发票主表
     inner join po_invoice_b pob2 on po2.pk_invoice = pob2.pk_invoice  where po2.dr = 0 and pob2.dr = 0 and csourcetypecode = '45' --采购发票子表
    ) z5 --采购发票信息
    on z2.订单明细id = z5.发票订单id and z5.发票入库单明细id = z4.入库单明细id  --采购发票关联订单和入库单
    left join 
    (select pk_order_b, nmny, pk_apply_b, 申请付款日期 from 
     (select po_order_payplan.pk_order_payplan, pk_order_b, po_order_payplan_b.vordercode, corderrowno, pk_order_payplan_b, po_order_payplan_b.nmny 
      from po_order_payplan_b  --付款计划子表
      inner join po_order_payplan on po_order_payplan.pk_order_payplan = po_order_payplan_b.pk_order_payplan --付款计划主表
      where po_order_payplan.dr = 0 and po_order_payplan_b.dr = 0
     ) a --付款计划信息
     inner join
     (select apb.sourcesystypecode, fsrcbilltypecode, pk_fsrcbillrowid 付款申请订单行id, ap.vbillno 申请单号, ap.applydate 申请付款日期, ap.dapprovedate 审批日期, apb.applymny 申请付款金额, orderno 订单号,
             ap.srcbillno 来源单据号, ap.pk_srcbilltypeid 来源单据类型, apb.srcbillno 明细来源单据号, apb.pk_srcbill 明细来源单据主键, srcbilltypecode 明细来源单据类型编码,
             pk_fsrcbillrowid, pk_apply_b
      from cmp_apply ap  --付款申请单主表
      inner join cmp_apply_b apb on ap.pk_apply = apb.pk_apply -- 付款申请单子表
      where ap.dr = 0 and apb.dr = 0 and fsrcbilltypecode = '21' 
     ) b --付款申请单信息
     on a.pk_order_payplan = b.pk_fsrcbillrowid  --付款申请单关联付款计划信息
     union
     (select pk_fsrcbillrowid as pk_order_b, apb.applymny as nmny, pk_apply_b, ap.applydate 申请付款日期 
      from cmp_apply ap  --付款申请单主表
      inner join cmp_apply_b apb on ap.pk_apply = apb.pk_apply 
      where ap.dr = 0 and apb.dr = 0 and fsrcbilltypecode = '21' and srcbilltypecode = 'F1' --付款申请单子表
     )
    ) z6  on z2.订单明细id = z6.pk_order_b  --付款申请单关联订单
    left join  
    (select top_itemid, a.billdate, money 
     from ap_paybill a inner join ap_payitem b on a.pk_paybill = b.pk_paybill 
     where a.dr = 0 and b.dr = 0 and top_billtype = '36D1'
    ) z7 on z6.pk_apply_b = z7.top_itemid
        """
        println(sql)
        def rows = conn.rows(sql)

        for (def row : rows) {
            if (row != null) {
                Map<String, String> item = new HashMap<>();
                item.put("cpraybillbid", row["订单请购单表体id"]?.toString());
                item.put("pk_order_b_order", row["订单明细id"]?.toString());
                item.put("dbilldate_order", row["订单日期"]?.toString());
                item.put("vbillcode_order", row["订单号"]?.toString());
                item.put("taudittime", row["订单审核日期"]?.toString());
                item.put("material_code", row["物料编码"]?.toString());
                item.put("material_name", row["物料名称"]?.toString());
                item.put("unit_name", row["计量单位"]?.toString());
                item.put("nnum_order", row["订单数量"]?.toString());
                item.put("nqtorigprice", row["订单无税单价"]?.toString());
                item.put("nqtorigtaxprice", row["订单含税单价"]?.toString());
                item.put("norigmny", row["订单无税金额"]?.toString());
                item.put("norigtaxmny", row["订单含税金额"]?.toString());
                item.put("ntax", row["订单税额"]?.toString());
                item.put("ntaxrate", row["订单税率"]?.toString());
                item.put("dplanarrvdate", row["计划到货日期"]?.toString());
                item.put("pk_order_b_arrive", row["到货单订单明细id"]?.toString());
                item.put("pk_arriveorder_b", row["到货单明细id"]?.toString());
                item.put("vbillcode_arrive", row["到货单号"]?.toString());
                item.put("dbilldate_arrive", row["到货日期"]?.toString());
                item.put("nnum_arrive", row["到货数量"]?.toString());
                item.put("ntaxmny_arrive", row["到货金额"]?.toString());
                item.put("csourcetype", row["入库单来源单据类型id"]?.toString());
                item.put("csourcebillbid", row["入库单来源单据id"]?.toString());
                item.put("cgeneralbid", row["入库单明细id"]?.toString());
                item.put("vbillcode_in", row["入库单号"]?.toString());
                item.put("dbilldate_in", row["入库单日期"]?.toString());
                item.put("nnum_in", row["入库数量"]?.toString());
                item.put("ntaxmny_in", row["入库金额"]?.toString());
                item.put("pk_order_b_invoice", row["发票订单id"]?.toString());
                item.put("csourcebid_invoice", row["发票入库单明细id"]?.toString());
                item.put("vbillcode_invoice", row["发票号"]?.toString());
                item.put("vordercode", row["vordercode"]?.toString());
                item.put("dbilldate_invoice", row["发票日期"]?.toString());
                item.put("nnum_invoice", row["发票数量"]?.toString());
                item.put("ntaxmny_invoice", row["发票金额"]?.toString());
                item.put("pk_order_b_payment", row["pk_order_b"]?.toString());
                item.put("nmny_payment", row["nmny"]?.toString());
                item.put("pk_apply_b", row["pk_apply_b"]?.toString());
                item.put("payment_date", row["申请付款日期"]?.toString());
                item.put("top_itemid", row["top_itemid"]?.toString());
                item.put("billdate", row["billdate"]?.toString());
                item.put("money", row["money"]?.toString());

                results.add(item);
            }
        }
        Map<String, List> pushData = new HashMap<>()
        pushData.put("items", results);
        println(pushData)
        push(pushData)
    } catch (Exception e) {
        e.printStackTrace()
    } finally {
        if (conn != null) {
            conn.close()
        }
    }
}
def push(data) {
    println "-------------------------采购订单任务启动-------------------------"
    try {
        // 请求信息 校验
        def items = data.get("items")

        // 在这里执行清空表的操作
        def conn = Sql.newInstance("jdbc:mysql://10.1.1.209:3306/sqlserver", "oper_dc", "operzADF2020qwer", "com.mysql.cj.jdbc.Driver")
        def truncateSql = "TRUNCATE TABLE procurement_and_payment_info"
        conn.execute(truncateSql)
        conn.close()

        for (def item : items) {
            def params = [:]
            params.put("cpraybillbid", item.get("cpraybillbid"))
            params.put("pk_order_b_order", item.get("pk_order_b_order"))
            params.put("dbilldate_order", item.get("dbilldate_order"))
            params.put("vbillcode_order", item.get("vbillcode_order"))
            params.put("taudittime", item.get("taudittime"))
            params.put("material_code", item.get("material_code"))
            params.put("material_name", item.get("material_name"))
            params.put("unit_name", item.get("unit_name"))
            params.put("nnum_order", item.get("nnum_order"))
            params.put("nqtorigprice", item.get("nqtorigprice"))
            params.put("nqtorigtaxprice", item.get("nqtorigtaxprice"))
            params.put("norigmny", item.get("norigmny"))
            params.put("norigtaxmny", item.get("norigtaxmny"))
            params.put("ntax", item.get("ntax"))
            params.put("ntaxrate", item.get("ntaxrate"))
            params.put("dplanarrvdate", item.get("dplanarrvdate"))
            params.put("pk_order_b_arrive", item.get("pk_order_b_arrive"))
            params.put("pk_arriveorder_b", item.get("pk_arriveorder_b"))
            params.put("vbillcode_arrive", item.get("vbillcode_arrive"))
            params.put("dbilldate_arrive", item.get("dbilldate_arrive"))
            params.put("nnum_arrive", item.get("nnum_arrive"))
            params.put("ntaxmny_arrive", item.get("ntaxmny_arrive"))
            params.put("csourcetype", item.get("csourcetype"))
            params.put("csourcebillbid", item.get("csourcebillbid"))
            params.put("cgeneralbid", item.get("cgeneralbid"))
            params.put("vbillcode_in", item.get("vbillcode_in"))
            params.put("dbilldate_in", item.get("dbilldate_in"))
            params.put("nnum_in", item.get("nnum_in"))
            params.put("ntaxmny_in", item.get("ntaxmny_in"))
            params.put("pk_order_b_invoice", item.get("pk_order_b_invoice"))
            params.put("csourcebid_invoice", item.get("csourcebid_invoice"))
            params.put("vbillcode_invoice", item.get("vbillcode_invoice"))
            params.put("vordercode", item.get("vordercode"))
            params.put("dbilldate_invoice", item.get("dbilldate_invoice"))
            params.put("nnum_invoice", item.get("nnum_invoice"))
            params.put("ntaxmny_invoice", item.get("ntaxmny_invoice"))
            params.put("pk_order_b_payment", item.get("pk_order_b_payment"))
            params.put("nmny_payment", item.get("nmny_payment"))
            params.put("pk_apply_b", item.get("pk_apply_b"))
            params.put("payment_date", item.get("payment_date"))
            params.put("top_itemid", item.get("top_itemid"))
            params.put("billdate", item.get("billdate"))
            params.put("money", item.get("money"))

            insertLog(params)
        }
        println "-------------------------传输完成-------------------------"
    } catch (Exception e) {
        e.printStackTrace();
        return ["state": 0, "Ascript": "请求参数错误,请检查"]
    }
}

static def insertLog(params) {
    def conn = null


    try {
        // 连接数据库...
        conn = Sql.newInstance("jdbc:mysql://10.1.1.209:3306/sqlserver", "oper_dc", "operzADF2020qwer", "com.mysql.cj.jdbc.Driver")


        def insertKeys = [] // 字段
        def insertValues = [] // 字段值
        def placeholders = [] // 占位符

        for (def entry : params) {
            insertKeys.add(entry.key)
            insertValues.add(entry.value)
            placeholders.add('?')
        }

        // 构建插入语句
        def sqlStatement = "INSERT INTO procurement_and_payment_info(" + insertKeys.join(",") + ") VALUES (" + placeholders.join(",") + ")"

        // 执行插入操作
        conn.execute(sqlStatement, insertValues)

        println "Record inserted successfully with pk_order_b_order :[${params.pk_order_b_order}]"
    } catch (Exception e) {
        e.printStackTrace()
        println "Error inserting record: ${e.message}"
    } finally {
        if (conn != null) {
            conn.close()
        }
    }
}